<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class T_product extends MY_Model{
	public function __construct()
	{
		parent::__construct();
		$this->table_name = T_PRODUCT;
	}

	/**
	 * get user by id
	 * @param  string  $select : fields of table need get
	 * @param  integer $id     id of user
	 * @return array|null
	 */
	public function get_data_by_id($id = -1, $select = '*'){
		if($id == -1 || !is_numeric($id)){
			return null;
		}

		if(strcmp($select, '*') == 0){
			$select = 'p.*'.',u.' . USER_NAME .',u.' . USER_AVATAR_PATH .',u.'.USER_RATE.',u.'.USER_PHONE_NUMBER .',u.' . USER_CREATE_DATE .' as memberSince' .', u.st_UserId as mi';
		}else{
			$select .= ',u.' . USER_NAME .',u.' . USER_AVATAR_PATH .',u.'.USER_RATE.',u.'.USER_PHONE_NUMBER .',u.'. USER_CREATE_DATE .' as memberSince';
		}

		$this->db->select($select);

		$this->db->where(PRODUCT_ID ." = $id LIMIT 1");
		$this->db->join( T_USERS.' as u', 'u.'. USER_ID .'='. 'p.'.PRODUCT_USER_ID);
		$result = $this->db->get($this->table_name . ' as p');
		$result = $result->result_array();
		return $result;
	}

	/**
	 * get data by property
	 * @param  string $select : fields of table need get
	 * @param  array  $where  conditions
	 * @return array|null
	 */
	public function get_data_by_property($where = array(), $select = '*' , $limit = 0){
		if($limit == -1){
			$this->db->select();
			$this->db->where($where);
			$query = $this->db->get($this->table_name);
			return $query->result_array();
		}
		if($where == null || !is_array($where)){
			return null;
		}
		
		// query
		if(strcmp($select, '*') == 0){
			$this->db->select();
		}else{
			$this->db->select($select);
		}
		$this->db->where($where);

		if($limit != 0 ){
			$arr = explode(',', $limit);
			if(empty($arr)|| count($arr) == 1){	
				$this->db->limit($limit);
			}else{
				$this->db->limit($arr[0], $arr[1]);
			}
			
		}else{
			$this->db->limit(50);
		}

		$result = $this->db->get($this->table_name);

		$result = $result->result_array();
		return $result;
	}

	/**
	 * insert data into talbe
	 * @param array   $data   array data insert
	 * @param integer $result if 0 return id inserted else 1 return data user inserted
	 * @return null|integer|array
	 */
	public function set_data($data = array(), $result = 0){
		if($data == null || !is_array($data)){
			return null;
		}

		// insert rows
		$this->db->insert($this->table_name, $data);
		// get id
		$last_id = $this->db->insert_id();
		//return
		if($result == 0){
			return $last_id;
		}else{
			return $this->get_data_by_id($last_id);
		}
	}

	/**
	 * update data by id
	 * @param  array   $data : data update
	 * @param  integer $id
	 * @return integer
	 */
	public function update_data_by_id($data = array(), $id = -1){
		if($data == null || !is_array($data) || $id == -1 || !is_numeric($id)){
			return null;
		}

		//condition
		$this->db->where(PRODUCT_ID ." = $id LIMIT 1");
		$this->db->update($this->table_name, $data);
		return $this->db->affected_rows();
	}

	/**
	 * update data
	 * @param  array  $data
	 * @param  array  $where : conditions update
	 * @return integer
	 */
	public function update_data_by_property($data = array(), $where = array()){
		if($data == null || !is_array($data) || $where == null || !is_array($where)){
			return null;
		}

		$this->db->where($where);
		$this->db->update($this->table_name, $data);
		return $this->db->affected_rows();
	}

	/**
	 * get total rows
	 * @return [type] [description]
	 */
	public function get_total_rows(){
		$number = parent::get_total_rows($this->table_name);
		return $number;
	}

	/**
	 * get max id of record
	 * @return [type] [description]
	 */
	public function get_max_id(){
		$this->db->select_max(PRODUCT_ID);
		$query = $this->db->get($this->table_name);
		// var_dump($query);exit();
		$query =  $query->result_array();
		return $query[0];
	}

	/**
	 * delete record product
	 * @param  integer $id [description]
	 * @return [type]      [description]
	 */
	public function unset_data_by_id($id = -1){
		if($id == -1){
			return 0;
		}

		$this->db->where(PRODUCT_ID, $id);
		$this->db->get($this->table_name);
	}

	public function get_product_completed($where = array()){

		if($where == null || !is_array($where)){
			return null;
		}
		$data['p.'. PRODUCT_USER_ID] = $where[PRODUCT_USER_ID];
		$data['p.'. PRODUCT_OFFER_ID . ' >= '] = 1;
		
		$this->db->select('p.*, o.*');
		$this->db->from($this->table_name . ' as p');
		$this->db->where($data);
		$this->db->join('offer as o', 'p.' . PRODUCT_ID .' = '. 'o.'. OFFER_PRODUCT_ID);
		$query = $this->db->get();

		$query = $query->result_array();
		return $query;
	}
	/**
	 * get hot product by number view
	 * @param  integer $page     [description]
	 * @param  integer $per_page [description]
	 * @return [type]            [description]
	 */
	public function get_hot_product($page = 0, $per_page = 50){
		$this->db->select();
		$this->db->where(PRODUCT_OFFER_ID . ' < ', 1);
		$this->db->order_by(PRODUCT_VIEW, "DESC");
		$this->db->limit($per_page, $page*$per_page);
		$result = $this->db->get($this->table_name);
		$result = $result->result_array();
		return $result;
	}

	public function get_product_distance($lat , $long ,$where , $limit = 0){
		$query_string = 'SELECT product.*,'.
			'userlist.'.USER_NAME.' as '.USER_NAME.' , userlist.'.USER_PHONE_NUMBER.' as '.USER_PHONE_NUMBER.' , userlist.'.USER_RATE.' as '.USER_PHONE_NUMBER.' ,'.
            '3956 * 2 * ASIN(SQRT( POWER(SIN(('.$lat.' - '.LATITUDE.') * pi()/180 / 2), 2) '.
            '+ COS('.$lat.' * pi()/180) * COS('.LATITUDE.' * pi()/180) *'.
            'POWER(SIN(('.$long.' - '.LONGITUDE.') * pi()/180 / 2), 2) )) '.
			'as distance FROM '.$this->table_name.' as product ,'.
			T_USERS.' as userlist '.
			'WHERE '.$where.' '.' AND userlist.'.USER_ID.' = product.'.USER_ID.' '.
           	'ORDER by distance ASC';
        if($limit != 0){
        	$query_string = $query_string.' LIMIT '.$limit;
        }else{
        	$query_string = $query_string.' LIMIT 50';
        }

        $result = $this->db->query($query_string);
        $result = $result->result_array();
        return $result;
	}

	public function get_product_distance_with_tag($lat , $long , $where , $tag_id , $category_id , $limit){

		$query_string_select = 'SELECT product.* ,'.
			'userlist.'.USER_NAME.' as '.USER_NAME.' , userlist.'.USER_PHONE_NUMBER.' as '.USER_PHONE_NUMBER.' , userlist.'.USER_RATE.' as '.USER_PHONE_NUMBER.' ,'.
			'3956 * 2 * ASIN(SQRT( POWER(SIN(('.$lat.' - '.LATITUDE.') * pi()/180 / 2), 2) '.
            '+ COS('.$lat.' * pi()/180) * COS('.LATITUDE.' * pi()/180) *'.
            'POWER(SIN(('.$long.' - '.LONGITUDE.') * pi()/180 / 2), 2) )) '.
			'as distance ';

		$query_string_from = 'FROM '.$this->table_name.' as product , '.
								T_USERS.' as userlist'.
								'(SELECT b.* , c.'.PRODUCT_ID.' FROM '.T_TAG.' as b , '.T_PRODUCT_TAG.' as c WHERE b.'.TAG_ID.' ='.$tag_id. ' AND b.'.TAG_ID.'= c.'.TAG_ID.') as tag_val ';
		$query_string_where='WHERE tag_val.'.PRODUCT_ID.' = '.$this->table_name.'.'.PRODUCT_ID.' '.
								' AND userlist.'.USER_ID.' = product.'.USER_ID.' ';

		if($category_id != -1){
			$query_string_where = $query_string_where.' '.$this->table_name.'.'.CATEGORY_ID.' = '.$category_id.' ';
		}

		if(!is_null($where) && !strlen($where) <2){
			$query_string_where = $query_string_where.' AND '.$where.' ';
		}
		
		$query_string_order_by = 'ORDER by distance ASC ';

		if(is_string($limit)&& strlen($limit) > 2){
			$query_string_limit = 'LIMIT '.$limit;
		}else{
			$query_string_limit = ' LIMIT 50';
		}
		

		$query_string = $query_string_select.
							$query_string_from.
							$query_string_where.
							$query_string_order_by.
							$query_string_limit;
        $result = $this->db->query($query_string);
        $result = $result->result_array();
        return $result;
	}

	public function get_product_distance_with_product_tag($lat , $long , $where , $product_tag_id , $category_id , $limit){

		$query_string_select = 'SELECT product.* ,'.
		'3956 * 2 * ASIN(SQRT( POWER(SIN(('.$lat.' - '.LATITUDE.') * pi()/180 / 2), 2) '.
            '+ COS('.$lat.' * pi()/180) * COS('.LATITUDE.' * pi()/180) *'.
            'POWER(SIN(('.$long.' - '.LONGITUDE.') * pi()/180 / 2), 2) )) '.
			'as distance ';

		$query_string_from = 'FROM '.$this->table_name.' as product , '.
								'(SELECT c.'.PRO_TAG_VALUE.',c.'.PRODUCT_ID.' FROM '.T_PRODUCT_TAG.' as c WHERE c.'.PRO_TAG_ID.' ='.$product_tag_id.' ) as tag_val ';
		$query_string_where='WHERE tag_val.'.PRODUCT_ID.' = '.$this->table_name.'.'.PRODUCT_ID.' ';
		if(!is_null($where) && !strlen($where) <2){
			$query_string_where = $query_string_where.' AND '.$where.' ';
		}
		
		$query_string_order_by = 'ORDER by distance ASC ';

		if(is_string($limit)&& strlen($limit) > 2){
			$query_string_limit = ' LIMIT '.$limit;
		}else{
			$query_string_limit = ' LIMIT 50';
		}

		$query_string = $query_string_select.
							$query_string_from.
							$query_string_where.
							$query_string_order_by.
							$query_string_limit;
        $result = $this->db->query($query_string);
        $result = $result->result_array();
        return $result;

	}

	public function get_history_offer($user_id = -1){
		$sql = "T.*,offer.st_offerprice FROM (SELECT T.*,product.st_ProductName,product.st_CreatedDate as NgayDang,product.st_price AS GiaDang,product.st_AvatarPath,(product.st_OfferId!=0) AS Daban,product.st_OfferId FROM (SELECT product.st_ProductId, MAX(Offer.st_CreatedDate) AS NgayOfferGanNhat, COUNT(st_OfferPrice) AS SoLuongOffer FROM product LEFT JOIN offer ON product.st_ProductId=offer.st_ProductId WHERE product.st_UserId=$user_id GROUP BY product.st_ProductId ) AS T JOIN product ON product.st_productid=T.st_productid) AS T LEFT JOIN offer ON T.st_offerid=offer.st_offerid order by NgayOfferGanNhat desc,NgayDang desc";
		$this->db->select($sql);
		$query = $this->db->get();
		$query = $query->result_array();
		return $query;
	}

}